package Dao;


import pojo.User;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao02 {

    private static Connection connection = null;
    private static PreparedStatement preparedStatement = null;
    private static ResultSet resultSet = null;


  /*  public UserDao02() {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
*/

    //改

    public int update(User user1, User user2) {
        connection = DBUtil.getConnection();

        int reslut = 0;
        String username1 = user1.getUsername();
        String username2 = user2.getUsername();
        String password1 = user1.getPassword();
        String password2 = user2.getPassword();
        String sql = "update J_user set username =?," +
                "password = ? " + "where username =? and password =?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username2);
            preparedStatement.setString(2, password2);
            preparedStatement.setString(3, username1);
            preparedStatement.setString(4, password1);
            reslut = preparedStatement.executeUpdate();


        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return -1;
        } finally {
            closeAll();
        }

        return reslut;
    }

    //删除


    public void delete(User user) {
        connection = DBUtil.getConnection();

        String sql = "delete from J_user where id =?";
        try {

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, user.getId());
            preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

    }

    //查询

    public List<User> query() {
        connection = DBUtil.getConnection();

        String sql = "select * from J_user";

        List<User> userList = new ArrayList<>();
        try {
            preparedStatement = UserDao02.connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                int state = resultSet.getInt("state");
                User user = new User(id, username, password, state);
                userList.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return userList;
    }

    //新增用户

    public int insert(User user) {
        connection = DBUtil.getConnection();
        try {

            String sql = "insert into J_USER (id,username,password,state)" +
                    " values (seq_j_user.nextval,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);


            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setInt(3, user.getState());
            int rs = preparedStatement.executeUpdate();
            return rs;


        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } finally {
            closeAll();
        }

    }

    public void closeAll() {
        try {
            if (resultSet != null)
                resultSet.close();
            if (preparedStatement != null)
                preparedStatement.close();
            if (connection != null)
                connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
